﻿using Dapper;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class Category_DAL
    {
        public IEnumerable<Category> SearchCategoryList()
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                const string query = "select * from system_content_basic_category where IsValid=1 order by CreateTime";
                return SqlMapper.Query<Category>(conn, query);
            }
        }

        public bool Add(Category category)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string insertSql = @"INSERT INTO System_Content_Basic_Category(Name,CreateTime,LastUpdateTime,IsValid) values(@Name,@CreateTime,@LastUpdateTime,@IsValid)";
                return conn.Execute(insertSql, new { @Name = category.Name, @CreateTime = category.CreateTime, @LastUpdateTime = category.LastUpdateTime, @IsValid=true }) > 0;
            }
        }

        public Category GetById(Int64 id)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string query = "select * from System_Content_Basic_Category where Id=" + id;
                return SqlMapper.Query<Category>(conn, query).FirstOrDefault();
            }
        }

        public bool Update(string name, Int64 categoryId)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string updateSql = "update System_Content_Basic_Category set Name=@Name,LastUpdateTime=@LastUpdateTime  where Id=@Id";
                return conn.Execute(updateSql, new { @Name = name, @Id = categoryId, @LastUpdateTime = DateTime.Now }) > 0;
            }
        }

        public bool Delete(Int64 categoryId)
        {
            using (IDbConnection conn = Hepler.MySqlOpenConnection())
            {
                string deleteSql = "update System_Content_Basic_Category set IsValid=0 where Id=@CategoryId";
                return conn.Execute(deleteSql, new { CategoryId = categoryId }) > 0;
            }
        }
    }
}
